<?php
Class Model_service extends CI_Model {
public function __construct()
{
$this->load->database('hms');
}
function tambah_service() {
$data = array(
'service' => $this->input->post('service_name'),
'kode' => $this->input->post('service_id'),
'tipe' => $this->input->post('service_type'),
'harga' => $this->input->post('service_price')
);
return $this->db->insert('service', $data);
}
function get_movie() {
    $this->db->order_by('service','ASC');
    $query = $getData = $this->db->get('service');
    if($getData->num_rows() > 0)
    return $query;
    else
    return null;
}
function get_service_edit($id_movie) {
    $this->db->order_by('service','ASC');
    $this->db->where('service_id',$service_id);
    $query = $getData = $this->db->get('service');   
    if($getData->num_rows() > 0)   
    return $query;  
    else  
    return null;       
}
function edit_service() {
    $service_id = $this->input->post('service');
    $data = array(
        'service' => $this->input->post('service_name'),
		'kode' => $this->input->post('service_id'),
		'tipe' => $this->input->post('service_type'),
		'harga' => $this->input->post('service_price')
    );  
        $this->db->where('service_id',$this->input->post('service_id',$service_id));
        $this->db->update('service', $data);       
}
function hapus_service($service_id) {
        $this->db->where('service_id',$service_id);
        $this->db->delete('service');  
}
   function get_receiptinvo($opo)
    {
		 
		$sql=" SELECT resv_checkin AS checkin, resv_checkout AS checkout, guest_name AS Name, room_number AS Room, resv_paym_total AS bayar_resv
FROM guest, reservation, reservation_payment
WHERE guest.guest_id = reservation.guest_id
and reservation.resv_id=".$opo." ";
        return $this->db->query($sql);         
    }       
   
   function get_resvrep()
    {
		$sql="SELECT RES.RESV_CHECKIN AS CHECKIN, RES.RESV_CHECKOUT AS CHECKOUT, RO.ROOM_NUMBER AS ROOM, RT.ROOM_TYPE_NAME AS 
TYPE , GUE.GUEST_NAME AS NAME, RES.RESV_STATUS AS STATUS 
FROM RESERVATION RES, ROOM RO, ROOM_TYPE RT, GUEST GUE, RESERVATION_ROOM RRO
WHERE RES.GUEST_ID = GUE.GUEST_ID
AND RES.ROOM_NUMBER = RO.ROOM_NUMBER
AND RT.ROOM_TYPE_ID = RO.ROOM_TYPE_ID
AND RRO.RESV_ID = RES.RESV_ID
group by RES.RESV_ID";
        return $this->db->query($sql);         
    }       

 function cetak_service($opo)
    {
		$sql="SELECT resv_serv_date as Date,guest_name as Name,room_number as Room,service_type_name as Type,service_name as Service,service_price as Harga,resv_serv_unit as Jumlah, resv_serv_unit*service_price as Total
        FROM guest,reservation,service,reservation_service,service_type
        WHERE reservation_service.service_id=service.service_id 
        and reservation_service.resv_id=reservation.resv_id 
        and reservation.guest_id=guest.guest_id 
        and service_type.service_type_id=service.service_type_id
        and reservation.resv_id=".$opo."" ;

        return $this->db->query($sql);         
    } 
	
	function getBiaya1($id){
        $sql = "select resv_id, sum(price) as total_room, sum(total) as total_service, ((sum(price)+sum(total)) + ((sum(price)+sum(total))*0.21) ) as total 
from (select resv_id, price, '' as total
from reservation_room 
union all
select resv_id, '' as price, resv_serv_total
from reservation_service ) a
where a.resv_id = ".$id;
        
        
        $hasil = $this->db->query($sql);
		return $hasil->row_array();
    }
	
	
		function getBiaya2($id){
        $sql = "select resv_id, sum(price) as total_room, sum(total) as total_service, (sum(price)+sum(total)) as total from (select resv_id, price, '' as total
from reservation_room 
union all
select resv_id, '' as price, resv_serv_total
from reservation_service ) a
where a.resv_id = ".$id;
        
        $hasil = $this->db->query($sql);
		return $hasil->row_array();
    }
		
		 function get_receipt($opo)
    {
		 
		$sql=" SELECT resv_checkin AS checkin, resv_checkout AS checkout, guest_name AS Name, room_number AS Room, guest_ktp AS ktp, guest_address AS alamat, guest_phone AS phone, resv_id AS nomer
FROM guest, reservation
WHERE guest.guest_id = reservation.guest_id
and reservation.resv_id=".$opo." ";
        return $this->db->query($sql);         
    }       
	
	 function cetak_service2($opo)
    {
		 
		$sql=" SELECT room.room_number AS room, room_type_name AS roomtype, service_name AS servicetype, room_type_price AS roomprice, service_price AS serviceprice, resv_serv_unit 	AS qty, room_type_price + resv_serv_unit * service_price + resv_paym_total AS Total
	FROM guest, reservation, service, reservation_service, service_type, room, room_type, 				reservation_payment
	WHERE reservation_service.service_id = service.service_id
		AND reservation_service.resv_id = reservation.resv_id
		AND reservation.guest_id = guest.guest_id
		AND service_type.service_type_id = service.service_type_id
       	and reservation.resv_id=".$opo." ";

        return $this->db->query($sql);         
    }       
}



?>